<?php
class Admin_Model_Content extends Application_Model_Db_Db
{
    /**
     * Get all posts available for current user
     * If type not empty, adding condition on category type
     * @param array $availableCategories
     * @param string $type
     * @return array
     */
    public function getAvailableContent($availableCategories, $type='') {
        $where = '';
        if (!empty($type)) {
            $where = "AND `type`.`type` = '{$type}'";
        }

        $sql = "SELECT
                    `content`.`id`,
                    `content`.`title`,
                    `content`.`is_visible`,
                    `content`.`date_create`,
                    IFNULL(`user`.`email`, 'Deleted'),
                    `category`.`category_name`,
                    IFNULL(`department`.`name`, 'Отсутствует') AS `department_name`
                FROM `content`
                LEFT JOIN `user`
                    ON `content`.`author_id` = `user`.`id`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id`
                INNER JOIN `category_type` AS `type`
                    ON `category`.`type_id` = `type`.`id`
                LEFT JOIN `department`
                    ON `category`.`department_id` = `department`.`id`    
                WHERE 
                    1
                    AND `category_id` IN ({$availableCategories})
                    {$where}
                ORDER BY `content`.`date_create` DESC";         
        return $this->_db->fetchAll($sql);
    }

    /** 
     * Validation data before post saving
     * 
     * @param array $data
     * @param int $currentId
     * @return array
     */
    public function validate($data, $currentId=0) {
        $errors = array();
        
        if (isset($data['alias']))
            $data['alias'] = ltrim(rtrim($data['alias']));        

        $data['title'] = ltrim(rtrim($data['title']));
        //title and text must be not null       
        if (empty($data['title']) or empty($data['text'])) {
            $errors['errors'] = 'title or text is empty';
            return $errors;
        }       
        
        if (isset($data['alias'])) {                                
            if (empty($data['alias'])) {            
                $data['alias'] = implode('-', explode(' ', mb_strtolower($data['title'], 'UTF-8')));
            } else {                
                $data['alias'] = implode('-', explode(' ', mb_strtolower($data['alias'], 'UTF-8')));                                                
            }   
             
            //alias must be unique
            if ($this->_aliasIsExist($data['alias'], $currentId)) {
                $errors['errors'] = 'alias already exists';
                return $errors;
            } else {
                //$regEx = '/^[a-zа-я0-9().-]*$/';            
                $regEx = '/^[a-zA-Z\p{Cyrillic}0-9()`’.\s\-]+$/u';
                if (!preg_match($regEx, $data['alias'])) {
                    $errors['errors'] = 'alias bad format';
                    return $errors;
                }
            }                                
        }        
        $data['alias'] = str_replace(array('(', ')'), '-', $data['alias']);        
        if (isset($data['date_create'])) {
            if (empty($data['date_create'])) {
                $data['date_create'] = date('Y-m-d H:i:s');
            } else {
                $data['date_create'] = date("Y-m-d H:i:s", strtotime($data['date_create']));//date_format($data['date_create'], 'Y-m-d H:i:s');
            }
        }

        $data['alias'] = str_replace('’', '', $data['alias']);       

        return $data;
    }

    public function uploadPreviewImage($adapter) { 
        $file    = $adapter->getFileInfo();          
        $key     = 'photo';
        $image   = new My_Images($file[$key]);        

        $adapter->addValidator('Extension', 
                               false, 
                               array(
                                   'extension' => 'jpg,gif,jpeg,png', 
                                   'case'      => false));
        $adapter->setDestination('public/uploads/preview_images');                                 
        
        $name = uniqid().'.'.$image->getImageExtension();        
        $size = $file[$key]['size'];        
        $adapter->addFilter('Rename', $name);

        if ($adapter->isUploaded($file[$key]['name']) 
            && $adapter->isValid($file[$key]['name'])) {            
            if ($adapter->receive($file[$key]['name'])) {                
                $uploadedImage = 'public/uploads/preview_images/' . $name;
                //if the size of image more than 200kb - reduce the size
                if ($image->sizeMoreThan(200)) {                    
                    $image->compressImage($uploadedImage, 70);
                }
                return $name;
            } 
        }
        return false;
    }
    
    /**
     * Get selected post data
     * @param int $postId
     * @return array
     */ 
    public function getPostData($postId) {
        $sql = "SELECT                    
                    `content`.`category_id`,
                    `content`.`preview_mediadata`,
                    `content`.`preview_text`,
                    `content`.`description`,
                    `content`.`keywords`,
                    `content`.`title`,
                    `content`.`text`,                    
                    `content`.`alias`,
                    `content`.`is_visible`,
                    DATE_FORMAT(`content`.`date_create`, '%Y/%m/%d') AS `date_create`,
                    GROUP_CONCAT(`tag`.`tag_name`) AS `tags`
                FROM `content`
                INNER JOIN `content_tag`
                    ON `content`.`id` = `content_tag`.`content_id`
                INNER JOIN `tag`
                    ON `content_tag`.`tag_id` = `tag`.`id`
                WHERE `content`.`id` = ?";
        return $this->_db->fetchRow($sql, $postId);
    }

    /**
     * Adding post
     * 
     * @param array $data
     * @return int id of the saved post
     */
    public function addPost($data) {
        $this->_db->insert('content', $data);
        return $this->_db->lastInsertId();      
    }
    
    /**
     * Updating post
     * @param array $data
     * @param int $postId
     * @return void
     */
    public function updatePost($data, $postId) {
        $where = 'id = ' . intval($postId);
        $this->_db->update('content', $data, $where);
    }
    
    /**     
     * @param string $tags
     * @param int $postId
     * @return void
     */
    public function savePostTags($tags, $postId) {
        $arrayTags = explode(',', $tags);
        foreach ($arrayTags as $tag) {                  
            //if tag already exist - associate tag with post
            if ($tagId = $this->_tagIsExist($tag)) {                
                $this->_linkTagToPost($tagId, $postId);
            //if tag not exist - save tag to database
            //and after associate tag with post
            } else {                
                $tagId = $this->addTag($tag);               
                $this->_linkTagToPost($tagId, $postId);
            }
            unset($tagId);
        }       
    }

    /**
     * @param int $postId
     * @return void 
     */
    public function deletePost($postId) {
        $where = 'id = ' . intval($postId);
        $this->_db->delete('content', $where);
        $this->deleteAssociateBetweenPostAndTags($postId); 
    }

    /**
     * Make visible or not visible selected post
     * @param string $status
     * @param int $postId
     */
    public function changeVisiblePost($status, $postId) {
        $data = array(
            'is_visible' => $status
        );
        $where = 'id = ' . intval($postId);
        $this->_db->update('content', $data, $where);
    }

    /**
     * Save tag to database
     * 
     * @param string $tag
     * @return int id of the saved tag
     */
    public function addTag($tag) {      
        $this->_db->insert('tag', array('tag_name' => $tag));
        return $this->_db->lastInsertId();
    }    
    
    /**
     * Associate tag with post
     * 
     * @param int $tagId
     * @param int $postId
     * @return void
     */
    protected function _linkTagToPost($tagId, $postId) {
        $data = array(
            'content_id' => $postId,
            'tag_id'     => $tagId
        );
        $this->_db->insert('content_tag', $data);
    }
    
    /**
     * @param int $postId
     * @return void
     */
    public function deleteAssociateBetweenPostAndTags($postId) {
        $where = 'content_id = ' . intval($postId);
        $this->_db->delete('content_tag', $where);
    }

    /**
     * Check for the existence of a tag
     * 
     * @param string $tag
     * @return int id of a tag
     */
    protected function _tagIsExist($tag) {
        $sql = "SELECT 
                    `id`
                FROM `tag`
                WHERE `tag_name` = '{$tag}'";
        return $this->_db->fetchOne($sql);
    }
    
    /**
     * Check for the existence of an alias
     * 
     * @param string $alias
     * @param int $currentId
     * @return int id of an alias 
     */
    protected function _aliasIsExist($alias, $currentId) {
        $sql = "SELECT
                   `id`
                FROM `content`
                WHERE 
                    1
                    AND `alias` = ?
                    AND `id` != ?";
        return $this->_db->fetchOne($sql, array($alias, $currentId));
    }

    public function getPostByCategoryId($categoryId) {
        $sql = "SELECT
                    `content`.`id`,
                    `content`.`title`,
                    `category`.`type_id`
                FROM `content`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id`
                WHERE 
                    1
                    AND `content`.`category_id` = ?";
        return $this->_db->fetchRow($sql, $categoryId);
    }
}